alter table jms_dwd.dwd_tmsnew_shipment_dt set tblproperties ('external.table.purge'='true');
drop table if exists jms_dwd.dwd_tmsnew_shipment_dt;
create external table if not exists  jms_dwd.dwd_tmsnew_shipment_dt (
    id                                       bigint           comment'主键'
    ,shipment_no                              string           comment'任务编号'
    ,vehicleline_type                         bigint           comment'运营模式'
    ,business_attribute                       bigint           comment'业务属性1:正班2:加班'
    ,shipment_name                            string           comment'任务名称'
    ,vehicleline_id                           bigint           comment'参考车线ID'
    ,vehicleline_code                         string           comment'参考车线编码'
    ,vehicleline_name                         string           comment'参考车线名称'
    ,shifts                                   bigint           comment'班次'
    ,transport_type                           string           comment'运力类型'
    ,operation_model                          bigint           comment'运行模式'
    ,vehicle_groupid                          bigint           comment'规划车型组ID'
    ,vehicle_typegroup                        string           comment'规划车型'
    ,start_id                                 bigint           comment'始发网点ID'
    ,start_code                               string           comment'始发网点编号'
    ,start_name                               string           comment'始发网点名称'
    ,end_id                                   bigint           comment'目的网点ID'
    ,end_code                                 string           comment'目的网点编号'
    ,end_name                                 string           comment'目的网点名称'
    ,cnt                                      bigint           comment'途径地数量'
    ,mileage                                  double           comment'系统里程(KM)'
    ,total_runtime                            bigint           comment'总运行时间(MIN)'
    ,total_intransittime                      bigint           comment'总在途时间(MIN)'
    ,span_days                                bigint           comment'跨越天数'
    ,standard_speed                           double           comment'标准时速(KM/H)'
    ,planned_departure_time                   timestamp        comment'规划发车时间'
    ,planned_arrival_time                     timestamp        comment'规划到车时间'
    ,actual_departure_time                    timestamp        comment'实际发车时间'
    ,actual_arrival_time                      timestamp        comment'实际到车时间'
    ,shipment_state                           bigint           comment'任务状态,待调度0已调度1待执行2在途3已完成4已作废5'
    ,apply_reason                             string           comment'申请原因'
    ,audit_status                             smallint         comment'审核状态'
    ,audit_remark                             string           comment'审核备注'
    ,audit_id                                 bigint           comment'审核人编号'
    ,auditer                                  string           comment'审核人'
    ,audit_date                               timestamp        comment'审核时间'
    ,vehicle_class_id                         string           comment'车种类ID'
    ,vehicle_class_name                       string           comment'车种类名称'
    ,plate_number                             string           comment'车牌号'
    ,trailer_number                           string           comment'挂车号'
    ,carrier_id                               bigint           comment'承运商ID'
    ,carrier_type                             smallint         comment'承运商类型'
    ,carrier_name                             string           comment'承运商全名'
    ,vehicletype_id                           string           comment'车型ID'
    ,vehicletype_name                         string           comment'车型名称'
    ,load_weight                              double           comment'核定载重(KG)'
    ,vehicle_door_cnt                         bigint           comment'车门'
    ,driver_name                              string           comment'司机名称'
    ,driver_contact                           string           comment'随车电话'
    ,shipment_type                            bigint           comment'任务类型1:自有正班2:外租正班3:自有加班4:外租加班'
    ,quotation_model                          bigint           comment'报价模式1加班价2正班价'
    ,create_by                                bigint           comment'创建人ID'
    ,create_by_name                           string           comment'创建人姓名'
    ,create_time                              timestamp        comment'创建时间'
    ,update_by                                bigint           comment'更新人ID'
    ,update_by_name                           string           comment'更新人姓名'
    ,update_time                              timestamp        comment'更新时间'
    ,apply_reason_item                        smallint         comment'申请原因固定选项(1临时加班、2顶班、3增加经停、4取消经停、5线路延伸、6其他)'
    ,pic_path                                 string           comment'微信上传任务图片（多张以,隔开）'
    ,pic_adr                                  string           comment'前端图片地址（多张以,隔开）'
    ,promotion                                tinyint          comment'是否促销：1:是，2：否'
    ,overtime_type                            tinyint          comment'加班类型：11爆仓加班、12顶班、13应急加班'
    ,overtime_reasons                         string           comment'加班原因说明'
    ,ori_reg_shift_carrier_id                 bigint           comment'原正班承运商ID'
    ,freight_generate_status                  tinyint          comment'运费流水生成状态：1已生产2未生成'
    ,billing_way                              tinyint          comment'计费方式（1：整车计重、2：零担计重、3：按趟计费）'
    ,settlement_vehicle_type_id               string           comment'结算车型ID'
    ,settlement_vehicle_type_name             string           comment'结算车型名称'
    ,car_apply_code                           string           comment'用车申请编码'
    ,bidding_demand_code                      string           comment'招标需求编码'
    ,is_ballast                               tinyint          comment'是否空放：1是2否'
    ,special_vehicle_type_id                  bigint           comment'特殊车型id'
    ,special_vehicle_type_name                string           comment'特殊车型名称'
) comment '运力平台-干线表'                                                                                                                              
partitioned by (dt string comment '分区字段-createtime创建时间')
stored as parquet
location '/dw/hive/jms_dwd.db/external/dwd_tmsnew_shipment_dt'
tblproperties (
'discover.partitions' = 'false',
'parquet.column.index.access' = 'true'
);
